package net.sky.oa.dao.asms.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sky.oa.dao.SkyBaseDaoImpl;
import net.sky.oa.dao.asms.AsmsDao;
import net.sky.oa.util.AsmsJdbcUtil;

import org.springframework.stereotype.Repository;

@SuppressWarnings("unchecked")
@Repository
public class AsmsDaoImpl extends SkyBaseDaoImpl<Object> implements AsmsDao {
	
	public List findStudentMobiles(int terId,int teaId,int onStatus) throws Exception {
		List list = new ArrayList();		
		Map<String,String> map = new HashMap<String, String>();
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		StringBuffer sb = new StringBuffer("");
		sb.append("select distinct sc.clh_incla_id claId, si.inf_name infName," +
				" coalesce(si.inf_mothermobile,si.inf_fathermobile,si.inf_personalmobile) mobile " +
				" from stu_classhistory sc left join stu_info si on  sc.clh_stu_id = si.inf_id");
		sb.append(" where sc.clh_tea_id =  "+teaId);
		
		if(terId!=-1&&terId!=0) {
			sb.append(" and sc.clh_ter_id = "+terId);
		}
		if(onStatus !=-1 && onStatus !=0) {
			sb.append(" and sc.clh_onstatus = "+onStatus);
		}
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sb.toString());		
		while(rs.next()) {
			map = new HashMap<String, String>();			
			map.put("infName", rs.getString("infName"));
			map.put("mobile", rs.getString("mobile"));
			list.add(map);
		}
		
		AsmsJdbcUtil.getInstance().release(rs, st, null, conn);
		return list;
	}
	
	/*
	 * 根据 学期、教师、班级、在读状态 查找学员map集合	 
	 */
	public List findStudentMobiles(String terIds,int teaId,int claId,int onStatus) throws Exception {
		
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		
		StringBuffer sb = new StringBuffer("");
		sb.append("select distinct sc.clh_incla_id claId,bc.cla_name claName,si.inf_name infName," +
				" coalesce(si.inf_mothermobile,si.inf_fathermobile,si.inf_personalmobile) mobile " +
				" from stu_classhistory sc left join stu_info si on  sc.clh_stu_id = si.inf_id " +
				" left join bas_class  bc on sc.clh_incla_id = bc.cla_id where bc.cla_status in (1,2,3)");
		sb.append(" and sc.clh_tea_id =  "+teaId);
		
		if(!"".equals(terIds)) {
			sb.append(" and sc.clh_ter_id in ("+terIds+")");//+terId);
		}
		
		if(claId!=-1&&claId!=0) {
			sb.append(" and sc.clh_incla_id = "+claId);
		}
		
		if(onStatus !=-1 && onStatus !=0) {
			sb.append(" and sc.clh_onstatus in (1,2,3)");
		}
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sb.toString());
		
		List classList = new ArrayList();
		
		int tempClaId = 0;
		List stuList = new ArrayList();
		
		Map classMap = null;
		while(rs.next()) {
			int classId = rs.getInt("claId");
			Map stuMap = new HashMap();
			if(classId!=tempClaId) {
				//第一个班级
				if(tempClaId==0) {
					classMap = new HashMap();
					stuList = new ArrayList();
					stuMap.put("infName", rs.getString("infName"));
					stuMap.put("mobile", rs.getString("mobile"));
					stuList.add(stuMap);
					classMap.put("claName", rs.getString("claName"));
					
				} else {//第N个班级
					//初始化下一个classMap
					classMap.put("stuList", stuList);
					classList.add(classMap);
					classMap = new HashMap();
					stuList = new ArrayList();
					
					stuMap.put("infName", rs.getString("infName"));
					stuMap.put("mobile", rs.getString("mobile"));
					stuList.add(stuMap);
					
					classMap.put("claName", rs.getString("claName"));
				}
				tempClaId = classId;
			} else {
				stuMap.put("infName", rs.getString("infName"));
				stuMap.put("mobile", rs.getString("mobile"));
				stuList.add(stuMap);
			}
		}
		
		if(null!=classMap){
			classMap.put("stuList", stuList);
			classList.add(classMap);
		}
		AsmsJdbcUtil.getInstance().release(rs, st, null, conn);
		return classList;
	}
	
	/*
	 * 根据  前学期 、 教师、班级、jiek状态 查找学员map集合 
	 */
	
	public List findHistoryStudentMobiles(String terId,int teaId,int claId,int onStatus) throws Exception {
		
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		
		StringBuffer sb = new StringBuffer("");
		sb.append("select distinct sc.clh_incla_id claId,bc.cla_name claName,si.inf_name infName," +
				" coalesce(si.inf_mothermobile,si.inf_fathermobile,si.inf_personalmobile) mobile " +
				" from stu_classhistory sc left join stu_info si on  sc.clh_stu_id = si.inf_id " +
				" left join bas_class  bc on sc.clh_incla_id = bc.cla_id where bc.cla_status in (1,2,3)");
		sb.append(" and sc.clh_tea_id =  "+teaId);
		
		if(!"".equals(terId)) {
			sb.append(" and sc.clh_ter_id in ("+terId+")");
		}
		
		if(claId!=-1&&claId!=0) {
			sb.append(" and sc.clh_incla_id = "+claId);
		}
		
		if(onStatus !=-1 && onStatus !=0) {
			sb.append(" and sc.clh_onstatus in (1,2,3)");//+onStatus);
		}
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sb.toString());
		
		List HistoryclassList = new ArrayList();
		
		int tempClaId = 0;
		List HistorystuList = new ArrayList();
		
		Map HistoryclassMap = null;
		while(rs.next()) {
			int classId = rs.getInt("claId");
			Map HistorystuMap = new HashMap();
			if(classId!=tempClaId) {
				//第一个班级
				if(tempClaId==0) {
					HistoryclassMap = new HashMap();
					HistorystuList = new ArrayList();
					HistorystuMap.put("infName", rs.getString("infName"));
					HistorystuMap.put("mobile", rs.getString("mobile"));
					HistorystuList.add(HistorystuMap);
					HistoryclassMap.put("claName", rs.getString("claName"));
					
				} else {//第N个班级
					//初始化下一个classMap
					HistoryclassMap.put("HistorystuList", HistorystuList);
					HistoryclassList.add(HistoryclassMap);
					HistoryclassMap = new HashMap();
					HistorystuList = new ArrayList();
					
					HistorystuMap.put("infName", rs.getString("infName"));
					HistorystuMap.put("mobile", rs.getString("mobile"));
					HistorystuList.add(HistorystuMap);
					
					HistoryclassMap.put("claName", rs.getString("claName"));
				}
				tempClaId = classId;
			} else {
				HistorystuMap.put("infName", rs.getString("infName"));
				HistorystuMap.put("mobile", rs.getString("mobile"));
				HistorystuList.add(HistorystuMap);
			}
		}
		if(null!=HistoryclassMap){
			HistoryclassMap.put("HistorystuList", HistorystuList);
			HistoryclassList.add(HistoryclassMap);
		}
		
		AsmsJdbcUtil.getInstance().release(rs, st, null, conn);
		return HistoryclassList;
	}
	/*
	 * 查看学期列表
	 */
	public List findTerms() throws Exception {
		List termList = new ArrayList();
		Map map = new HashMap();
		String sql = "select t.ter_id,t.ter_name from bas_term t "+
				" order by t.ter_begintime desc";
		
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		
		while(rs.next()) {
			map = new HashMap();
			map.put("terId", rs.getInt("ter_id"));
			map.put("terName", rs.getString("ter_name"));
			termList.add(map);
		} 
		return termList;
	}
	
	/*
	 * 获取当前学期的前两个学期
	 */
	 
    public String findHistoryTerms() throws Exception{
    	StringBuffer terId=new StringBuffer("");
//    	List termList= new ArrayList();
    	String sql="select ter_id,ter_name from (select t.ter_id,t.ter_name from bas_term t " +
    			" where sysdate > t.ter_endtime order by t.ter_endtime desc)";
    	/*
    	String sql ="select tt.ter_id,tt.ter_name from bas_term tt " +
    			"where tt.ter_begintime<(select t.ter_begintime from bas_term t" +
    			" where to_char(sysdate,'yyyy-MM-dd') between " +
    			"to_char(t.ter_begintime,'yyyy-MM-dd')  " +
    			"and to_char(t.ter_endtime,'yyyy-MM-dd')  ) " +
    			"order by tt.ter_endtime desc";*/
    	Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
				
		int i=0;
		while(rs.next()) {
			if(i>2)break;
			terId.append(rs.getInt("ter_id")+",");
			i++;
//			map.put("terId", rs.getInt("ter_id"));
//			map.put("terName", rs.getString("ter_name"));
		}
		int index=terId.lastIndexOf(",");
		terId.deleteCharAt(index);
		return terId.toString();
    } 
	/*
	 * 获取当前学期及后一学期
	 */
	public String findCurrentTerm() throws Exception {
		//List map = new HashMap();
		StringBuffer terIds=new StringBuffer("");
		String sql = "select t.ter_id,t.ter_name from bas_term t  " +
				" where (sysdate between t.ter_begintime and  t.ter_endtime )" +
				" or sysdate < t.ter_begintime  order by t.ter_begintime asc";
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		
		int i=0;
		while(rs.next()) {
			if(i>1)break;
			terIds.append(rs.getInt("ter_id")+",");
			i++;
//			map.put("terId", rs.getInt("ter_id"));
//			map.put("terName", rs.getString("ter_name"));
		}
		int index=terIds.lastIndexOf(",");
		terIds.deleteCharAt(index);
		AsmsJdbcUtil.getInstance().release(rs, st, null, conn);
		return terIds.toString();
	}

	/*
	 * 根据手机号码  查看学员姓名
	 */
	public String findStuNameByMobile(String mobile) throws Exception {
		String sql = "select inf_name from stu_info si "+
			" where coalesce(si.inf_mothermobile,si.inf_fathermobile,si.inf_personalmobile) = "+mobile;		
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		
		String stuName = "";
		while(rs.next()) {
			stuName = stuName + rs.getString("inf_name")+" ";
		}
		AsmsJdbcUtil.getInstance().release(rs, st, null, conn);
		return stuName;
	}
	
	
	/*
	 * 根据OA用户姓名 查看学员管理教师ID
	 */
	public long findTeaId(String teaName) throws Exception {
		String sql = "select tea_id from bas_teacher where tea_name = '"+teaName.trim()+"'";
		
		Connection conn = AsmsJdbcUtil.getInstance().getConnection();
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		
		long teaId = 0;
		while(rs.next()) {
			teaId = rs.getLong("tea_id");
		}
		AsmsJdbcUtil.getInstance().release(rs, st, null, conn);
		
		return teaId;
		
	}

}
